package com.weishao.test;

import java.sql.JDBCType;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.42</version>
		</dependency>
		
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>42.2.5</version>
		</dependency>
		
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc7</artifactId>
			<version>12.1.0.2</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/lib/ojdbc7-12.1.0.2.jar</systemPath>
		</dependency>

		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>sqljdbc4</artifactId>
			<version>4.0</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/lib/sqljdbc4-4.0.jar</systemPath>
		</dependency>
		
		<dependency>
			<groupId>com.pivotal</groupId>
			<artifactId>greenplum-jdbc</artifactId>
			<version>5.1.4</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/lib/greenplum-jdbc-5.1.4.jar</systemPath>
		</dependency>
		
*/
public class JdbcMetaTest {

	public static void main(String[] args) {
		try {
			//String url = "jdbc:mysql://172.16.90.210:3306/test_for_tang?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&nullCatalogMeansCurrent=true";
			//String driver = "com.mysql.jdbc.Driver";
			//String user = "tangyibo";
			//String password = "tangyibo";
			//String catalog=null;
			//String schema=null;
			
			//String url = "jdbc:oracle:thin:@172.16.90.152:1521:helowin";
			//String driver = "oracle.jdbc.driver.OracleDriver";
			//String user = "tangyibo";
			//String password = "tangyibo";
			//String catalog=null;
			//String schema="TANGYIBO";

			String url = "jdbc:sqlserver://172.16.90.166:1433;DatabaseName=hqtest";
			String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
			String user = "hqtest";
			String password = "123456";
			String catalog=null;
			String schema="dbo";
			
			//String url = "jdbc:postgresql://172.16.90.210:5432/tangyibo";
			//String driver = "org.postgresql.Driver";
			//String user = "tangyibo";
			//String password = "tangyibo";
			//String catalog=null;
			//String schema="public";
			
			//String url = "jdbc:pivotal:greenplum://172.16.90.151:5432;DatabaseName=study";
			//String driver = "com.pivotal.jdbc.GreenplumDriver";
			//String user = "study";
			//String password = "123456";
			//String catalog=null;
			//String schema="public";
			
			Class.forName(driver);
			Connection connection = DriverManager.getConnection(url, user, password);
			DatabaseMetaData meta = connection.getMetaData();
			
			ResultSet schemas=meta.getSchemas();
			while (schemas.next()) {
				 System.out.println(schemas.getString("TABLE_SCHEM")+"|"+schemas.getString("TABLE_CATALOG"));
			}
			
			ResultSet tables = meta.getTables(catalog, schema,"%",new String[]{"TABLE","VIEW"}); 
			while (tables.next()) {
				 System.out.println(tables.getString("TABLE_NAME")+"|"+tables.getString("TABLE_TYPE"));
			}
			
			ResultSet columns = meta.getColumns( catalog,schema, "org_view"/*"yktxt_yktjyxx_V_ZJK_YKT_JYXX"*/, null );
			while (columns.next()) {
				String column_name = columns.getString("COLUMN_NAME");
				String type_name = columns.getString("TYPE_NAME");
				int column_size = columns.getInt("COLUMN_SIZE");
				int data_type=columns.getInt("DATA_TYPE");
				int decimal_digits=columns.getInt("DECIMAL_DIGITS");
				int nullable = columns.getInt("NULLABLE"); 
				
				Map<String,Object > cols=new HashMap<String,Object >();
				cols.put("COLUMN_NAME",column_name);
				cols.put("TYPE_NAME",type_name);
				cols.put("COLUMN_SIZE",column_size);
				cols.put("DATA_TYPE_NAME",JDBCType.valueOf(data_type));
				cols.put("DATA_TYPE_VALUE",data_type);
				cols.put("DECIMAL_DIGITS",decimal_digits);
				cols.put("NULLABLE",nullable);
				System.out.println(cols);
			}

			ResultSet primarykeys = meta.getPrimaryKeys(catalog, schema, "org_view");
			while (primarykeys.next()) {
				System.out.println(primarykeys.getString("COLUMN_NAME"));
			}
			
			System.out.println("We are using " + meta.getDatabaseProductName());
			System.out.println("Version is " + meta.getDatabaseProductVersion());
			
			connection.close();
		} catch (Exception e) {
			System.err.println(e);
		}
	}
}
